﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_Review.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_Review*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_Review
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_Review obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "Review ([UserID],[ReviewTitle],[ReviewContent],[ProductID],[IsShow],[IsTop],[IsDelete],[AddTime],[rateStatus])");
            sql.Append("values(@UserID,@ReviewTitle,@ReviewContent,@ProductID,@IsShow,@IsTop,@IsDelete,@AddTime,@rateStatus)");
            sql.Append(";SELECT @@IDENTITY");

            SqlParameter[] pra = {
                            new SqlParameter("@UserID",obj.UserID),
							new SqlParameter("@ReviewTitle",obj.ReviewTitle),
							new SqlParameter("@ReviewContent",obj.ReviewContent),
							new SqlParameter("@ProductID",obj.ProductID),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@IsTop",obj.IsTop),
							new SqlParameter("@IsDelete",obj.IsDelete),
							new SqlParameter("@AddTime",obj.AddTime),
                            new SqlParameter("@rateStatus",obj.rateStatus)
                                 };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        public int Update(Model.CNVP_Review obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Review set [UserID] = @UserID,[ReviewTitle] = @ReviewTitle,[ReviewContent] = @ReviewContent,[ProductID] = @ProductID,[LastReplyAuthor] = @LastReplyAuthor,[LastReplyTime] = @LastReplyTime,[LastReplyContent]=@LastReplyContent,[IsShow] = @IsShow,[IsTop] = @IsTop,[IsDelete] = @IsDelete,[AddTime] = @AddTime where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@UserID",obj.UserID),
							new SqlParameter("@ReviewTitle",obj.ReviewTitle),
							new SqlParameter("@ReviewContent",obj.ReviewContent),
							new SqlParameter("@ProductID",obj.ProductID),
							new SqlParameter("@LastReplyAuthor",obj.LastReplyAuthor),
							new SqlParameter("@LastReplyTime",obj.LastReplyTime),
							new SqlParameter("@LastReplyContent",obj.LastReplyContent),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@IsTop",obj.IsTop),
							new SqlParameter("@IsDelete",obj.IsDelete),
							new SqlParameter("@AddTime",obj.AddTime)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        public int Reply_CNVP_Review(Model.CNVP_Review obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Review set [LastReplyAuthor] = @LastReplyAuthor,[LastReplyTime] = @LastReplyTime,[LastReplyContent]=@LastReplyContent where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@LastReplyAuthor",obj.LastReplyAuthor),
							new SqlParameter("@LastReplyTime",obj.LastReplyTime),
							new SqlParameter("@LastReplyContent",obj.LastReplyContent)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        public int ChangeReviewStatTrueBySet(string field, string rId)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Review set [" + field + "] = 1 where [ID] in (" + rId + ")");
             return DbHelper.ExecuteNonQuery(sql.ToString());
        }
        public int ChangeReviewStatFalseBySet(string field, string rId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Review set [" + field + "] = 0 where [ID] in (" + rId + ")");
            return DbHelper.ExecuteNonQuery(sql.ToString());
        }
        public int DeleteReviewBySet(string rId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + Prefix + "Review where  [ID] in (" + rId + ")");
            return DbHelper.ExecuteNonQuery(sql.ToString());
        }
        public int CleanUpDeletedReview()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + Prefix + "Review where  [IsDelete]=1");
            return DbHelper.ExecuteNonQuery(sql.ToString());
        }
        public int ChangeReviewStatTrue(string field, int rId)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Review set [" + field + "] = 1 where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",rId)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        public int ChangeReviewStatFalse(string field, int rId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Review set [" + field + "] = 0 where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",rId)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
        public int Delete(Model.CNVP_Review obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "Review where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }
        public int AddToRecycle(int  rId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update [" + Prefix + "Review] set [IsDelete]=1 where [ID] = @ID");
            SqlParameter[] pra = {
                                new SqlParameter("@ID",rId)
						
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
        public int GetCountOfReview(int id)
        {
            string sql = @"select count(*) as ReviewLength from " + Prefix + "Review where [ProductID] = @ProductID and [IsShow]=1 and [Isdelete]=0";
            System.Data.SqlClient.SqlParameter[] pra = {
                                                           CNVP.Framework.Helper.DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, id)
                                };
            DataTable dt=CNVP.Framework.Helper.DbHelper.ExecuteTable(sql.ToString(),pra);
            if (dt == null || dt.Rows.Count < 1)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(dt.Rows[0]["ReviewLength"]);
            }
        }

        public List<Model.CNVP_Review> LoadAll(int ProductID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[UserID],[ReviewTitle],[ReviewContent],[LastReplyAuthor],[LastReplyTime],[LastReplyContent],[IsShow],[IsTop],[IsDelete],[AddTime],[rateStatus] from " + Prefix + "Review");
            sql.Append(" where [ProductID] =" + ProductID + "");
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Review> list = new List<Model.CNVP_Review>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Review cnvp_review = new Model.CNVP_Review();
                    cnvp_review.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_review.UserID = dr["UserID"] is DBNull ? "游客" : (string)dr["UserID"];
                    cnvp_review.ReviewTitle = dr["ReviewTitle"] is DBNull ? String.Empty : (string)dr["ReviewTitle"];
                    cnvp_review.ReviewContent = dr["ReviewContent"] is DBNull ? String.Empty : (string)dr["ReviewContent"];
                    cnvp_review.LastReplyAuthor = dr["LastReplyAuthor"] is DBNull ? String.Empty : (string)dr["LastReplyAuthor"];
                    cnvp_review.LastReplyTime = dr["LastReplyTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastReplyTime"];
                    cnvp_review.LastReplyContent = dr["LastReplyContent"] is DBNull ? String.Empty : (string)dr["LastReplyContent"];
                    cnvp_review.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_review.IsTop = dr["IsTop"] is DBNull ? (byte)0 : (byte)dr["IsTop"];
                    cnvp_review.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_review.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_review.rateStatus = dr["rateStatus"] is DBNull ? (byte)0 : (byte)dr["rateStatus"];
                    list.Add(cnvp_review);
                }
            }
            return list;

        }

        public List<Model.CNVP_Review> LoadAll(int ProductID, string rateStatus)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[UserID],[ReviewTitle],[ReviewContent],[LastReplyAuthor],[LastReplyTime],[LastReplyContent],[IsShow],[IsTop],[IsDelete],[AddTime],[rateStatus] from " + Prefix + "Review");
            sql.Append(" where [ProductID] =" + ProductID + " and [rateStatus] in (" + rateStatus + ")");
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Review> list = new List<Model.CNVP_Review>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Review cnvp_review = new Model.CNVP_Review();
                    cnvp_review.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_review.UserID = dr["UserID"] is DBNull ? "游客" : (string)dr["UserID"];
                    cnvp_review.ReviewTitle = dr["ReviewTitle"] is DBNull ? String.Empty : (string)dr["ReviewTitle"];
                    cnvp_review.ReviewContent = dr["ReviewContent"] is DBNull ? String.Empty : (string)dr["ReviewContent"];
                    cnvp_review.LastReplyAuthor = dr["LastReplyAuthor"] is DBNull ? String.Empty : (string)dr["LastReplyAuthor"];
                    cnvp_review.LastReplyTime = dr["LastReplyTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastReplyTime"];
                    cnvp_review.LastReplyContent = dr["LastReplyContent"] is DBNull ? String.Empty : (string)dr["LastReplyContent"];
                    cnvp_review.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_review.IsTop = dr["IsTop"] is DBNull ? (byte)0 : (byte)dr["IsTop"];
                    cnvp_review.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_review.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_review.rateStatus = dr["rateStatus"] is DBNull ? (byte)0 : (byte)dr["rateStatus"];
                    list.Add(cnvp_review);
                }
            }
            return list;

        }

        public Model.CNVP_Review LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [UserID],[ReviewTitle],[ReviewContent],[ProductID],[LastReplyAuthor],[LastReplyTime],[LastReplyContent],[IsShow],[IsTop],[IsDelete],[AddTime],[rateStatus] from " + Prefix + "Review");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_Review cnvp_review = new Model.CNVP_Review();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_review.UserID = dr["UserID"] is DBNull ? "游客" : (string)dr["UserID"];
                cnvp_review.ReviewTitle = dr["ReviewTitle"] is DBNull ? String.Empty : (string)dr["ReviewTitle"];
                cnvp_review.ReviewContent = dr["ReviewContent"] is DBNull ? String.Empty : (string)dr["ReviewContent"];
                cnvp_review.ProductID = dr["ProductID"] is DBNull ? 0 : Convert.ToInt32(dr["ProductID"]);
                cnvp_review.LastReplyAuthor = dr["LastReplyAuthor"] is DBNull ? String.Empty : (string)dr["LastReplyAuthor"];
                cnvp_review.LastReplyTime = dr["LastReplyTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastReplyTime"];
                cnvp_review.LastReplyContent = dr["LastReplyContent"] is DBNull ? String.Empty : (string)dr["LastReplyContent"];
                cnvp_review.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                cnvp_review.IsTop = dr["IsTop"] is DBNull ? (byte)0 : (byte)dr["IsTop"];
                cnvp_review.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_review.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_review.rateStatus = dr["rateStatus"] is DBNull ? (byte)0 : (byte)dr["rateStatus"];
            }
            return cnvp_review;
        }

        #endregion

        #region 分页 DAL
        public DataTable GetReviewPage(string PageName,int PageIndex, int PageSize,int ProductID, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string AllFields = "[ReviewTitle],[ReviewContent],[LastReplyAuthor] ,[LastReplyTime],[LastReplyContent],[AddTime],[UserID],[rateStatus]";
                string Condition = "[" + Prefix + "Review] where [IsShow]=1 and [IsDelete]=0 and [ProductID]=" + ProductID;
                string IndexField = "ID";
                string OrderFields = "order by [IsTop] desc,Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        public DataTable GetUserReviewPage(string PageName,int PageIndex, int PageSize, out int RecordCount, out int PageCount, string userID)
        {
            try
            {
                string AllFields = "[ID],[ReviewTitle],[ReviewContent],[LastReplyAuthor] ,[LastReplyTime],[LastReplyContent],[AddTime],[UserID],[ProductID],[rateStatus]";
                string Condition = "[" + Prefix + "Review] where [IsShow]=1 and [IsDelete]=0 and [UserID]='" + userID+"'";
                string IndexField = "ID";
                string OrderFields = "order by [IsTop] desc,Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        public DataTable GetReviewAll(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        {
            try
            {
                string AllFields = "[ID],[ReviewTitle],[ReviewContent],[LastReplyAuthor] ,[LastReplyTime],[LastReplyContent],[AddTime],[UserID],[ProductID],[IsTop],[IsShow],[rateStatus]";
                string Condition = "[" + Prefix + "Review] where [IsDelete]=0 ";
                string IndexField = "ID";
                string OrderFields = "order by [IsTop] desc,Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        public DataTable GetReviewForRecycleAll(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        {
            try
            {
                string AllFields = "[ID],[ReviewTitle],[ReviewContent],[LastReplyAuthor] ,[LastReplyTime],[LastReplyContent],[AddTime],[UserID],[ProductID],[IsTop],[IsShow],[rateStatus]";
                string Condition = "[" + Prefix + "Review] where [IsDelete]=1";
                string IndexField = "ID";
                string OrderFields = "order by [IsTop] desc,Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        public DataTable GetRateStatusReviewPage(string PageName, int PageIndex, int PageSize, int ProductId, out int RecordCount, out int PageCount, string rateStatus)
        {
            try
            {
                string AllFields = "[ID],[ReviewTitle],[ReviewContent],[LastReplyAuthor] ,[LastReplyTime],[LastReplyContent],[AddTime],[UserID],[ProductID],[rateStatus]";
                string Condition = "[" + Prefix + "Review] where [IsShow]=1 and [IsDelete]=0 and [ProductID]=" + ProductId + " and [rateStatus] in (" + rateStatus + ")";
                string IndexField = "ID";
                string OrderFields = "order by [IsTop] desc,Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        #endregion
    }
}

